﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace Model {
    internal class DBHelper {
        //DBHelperProj2更新
        //学校小组项目，尚且没学能隐藏数据库连接字符串的方法，请不要修改我们的数据库，谢谢。
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnString { private get; set; } = Joker.Properties.Settings.Default.JokerConnectionString;

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        public static SqlConnection Conn = null;

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        public static void InitConnection() {
            //对象为空，则创建
            if (Conn == null) Conn = new SqlConnection(ConnString);
            //连接中断，则重连
            if (Conn.State == ConnectionState.Broken) Conn.Close();
            //连接关闭，则打开
            if (Conn.State == ConnectionState.Closed) Conn.Open();
        }

        /// <summary>
        /// 执行SQL查询语句，返回DataTable对象
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string sqlStr) {
            InitConnection();
            DataTable dataTable = new DataTable();
            new SqlDataAdapter(sqlStr, Conn).Fill(dataTable);
            Conn.Close();
            return dataTable;
        }
        public static DataSet GetDataSet(string sql) {
            InitConnection();
            DataSet dataSet = new DataSet();
            new SqlDataAdapter(sql, Conn).Fill(dataSet);
            Conn.Close();
            return dataSet; 
        }

        /// <summary>
        /// 执行SQL增删改语句，返回执行结果(成功/失败)
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>执行结果</returns>
        public static bool ExecuteNonQuery(string sqlStr) {
            InitConnection();
            int result = new SqlCommand(sqlStr, Conn).ExecuteNonQuery();
            Conn.Close();
            return result > 0;
        }

        /// <summary>
        /// 执行集合函数,返回第一行第一列数据
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>数据</returns>
        public static object ExecuteScalar(string sqlStr) {
            InitConnection();
            object result = new SqlCommand(sqlStr, Conn).ExecuteScalar();
            Conn.Close();
            return result;
        }

        /// <summary>
        /// 执行SQL查询语句，返回DataReader对象
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader GetDataReader(string sqlStr) {
            InitConnection();
            //CommandBehavior.CloseConnection 命令行为：当DataReader对象被关闭时，自动关闭占用的连接对象
            return new SqlCommand(sqlStr, Conn).ExecuteReader(CommandBehavior.CloseConnection);
        }
    }
}
